package main.reports;

import main.Trees.SmetNode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashMap;

/**
 * Created by s_shmakov on 21.05.14.
 */
public class XLSReportProgressSmet {
    private SmetNode root;
    private String pathXLSFile;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private int rownum;
    private HashMap<String, CellStyle> styles;

    public String generate(SmetNode root) {
        this.root = root;
        chooseFile();
        return pathXLSFile;
    }

    private void chooseFile() {
        JFileChooser fileChooser = new JFileChooser();
        File fle = new File("Отчет по прогрессу привязки смет объекта "+root.getName() + ".xls");
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
            if(!pathXLSFile.toLowerCase().endsWith(".xls"))
            {
                pathXLSFile = pathXLSFile + ".xls";
            }
            generateXLS();
        }
    }

    private void generateXLS() {
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("LinkList");
        rownum = 0;

        sheet.setRowSumsBelow(false);

        createStyles();
        addTitle();

        getRow(root);

        //region Устанавливаем ширины столбцов в Excel
        sheet.setColumnWidth(0, 256*9);
        sheet.setColumnWidth(1, 256*90);
        sheet.setColumnWidth(2, 256*10);
        sheet.setColumnWidth(3, 256*10);
        sheet.setColumnWidth(4, 256*10);
        //endregion

        FileOutputStream out;
        try {
            out = new FileOutputStream(new File(pathXLSFile));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //region Стили
    private void createStyles() {
        styles = new HashMap<>();

        CellStyle style;

        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short)57, (byte)217, (byte)217, (byte)217);

        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short)10);
        mainFont.setFontName("Calibri");

        HSSFFont boldFont = wb.createFont();
        boldFont.setFontHeightInPoints((short)10);
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldFont.setFontName("Calibri");

        HSSFFont whiteFont = wb.createFont();
        whiteFont.setFontHeightInPoints((short)10);
        whiteFont.setColor(IndexedColors.WHITE.getIndex());
        whiteFont.setFontName("Calibri");

        HSSFFont whiteBoldFont = wb.createFont();
        whiteBoldFont.setFontHeightInPoints((short) 10);
        whiteBoldFont.setColor(IndexedColors.WHITE.getIndex());
        whiteBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        whiteBoldFont.setFontName("Calibri");

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_bottom_border", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_bottom_right_border", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_borders", style);

        palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        style.setWrapText(true);
        styles.put("root", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        style.setWrapText(true);
        styles.put("wbs0", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        style.setWrapText(true);
        styles.put("wbs1", style);

        palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        styles.put("root_left", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        HSSFCreationHelper creationHelper = wb.getCreationHelper();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0%"));
        styles.put("wbs0_left", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0"));
        styles.put("wbs0_num", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0%"));
        styles.put("wbs1_left", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0"));
        styles.put("wbs1_num", style);

        style = wb.createCellStyle();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0%"));
        styles.put("task_left", style);

        style = wb.createCellStyle();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("0"));
        styles.put("task_num", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_double", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_double", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left_grey", style);
    }
    //endregion

    //region Добавление заголовка
    private void addTitle () {
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(15.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellValue("№");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(1);
        cell.setCellValue("Наименование");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(2);
        cell.setCellValue("Всего");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(3);
        cell.setCellValue("Привязано");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(4);
        cell.setCellValue("%");
        cell.setCellStyle(styles.get("topTitle_borders"));
    }
    //endregion

    //region Перебор строк и добавление
    private void getRow (SmetNode curNode) {
        Row row = sheet.createRow(rownum++);
        Cell cell;


        for (int col = 0; col < 11; col++) {
            cell = row.createCell(col);
            switch (col) {
                case 0:
                    cell.setCellValue(curNode.getId());
                    break;
                case 1:
                    cell.setCellValue(curNode.getName());
                    break;
                case 2:
                    cell.setCellValue(curNode.getUnit());
                    break;
                case 3:
                    cell.setCellValue(curNode.getSmNum());
                    break;
                case 4:
                    cell.setCellValue(curNode.getVolume() != null ? curNode.getVolume().divide(new BigDecimal(100)).doubleValue() : 0);
                    break;
                  }

            if (curNode.isString) {
                setSmrStyle(curNode, cell, col);
            } else if (curNode.isHeader){
                setHeadStyle(curNode, cell, col);
            } else {
                setObjStyle(curNode, cell, col);
            }
        }

        if (curNode.getChildren() == null) return;

        int groupStart = rownum;

        for (SmetNode child : curNode.getChildren()) {
            getRow(child);
        }

        sheet.groupRow(groupStart, rownum);

    }
    //endregion

    private void setSmrStyle(SmetNode node, Cell cell, int col) {
        if (col == 4) {
            cell.setCellStyle(styles.get("task_left"));
        } else if (col == 2 || col == 3) {
            cell.setCellStyle(styles.get("task_num"));
            cell.setCellValue(Integer.parseInt(cell.getStringCellValue()));
        }
    }

    private void setObjStyle(SmetNode node, Cell cell, int col) {
        if (col == 0) {
            cell.setCellStyle(styles.get("wbs0"));
        } else if (col == 1) {
            cell.setCellStyle(styles.get("wbs0"));
        } else if (col == 2 || col == 3) {
            cell.setCellStyle(styles.get("wbs0_num"));
            cell.setCellValue(Integer.parseInt(cell.getStringCellValue()));
        } else if (col == 4) {
            cell.setCellStyle(styles.get("wbs0_left"));
        }
    }

    private void setHeadStyle(SmetNode node, Cell cell, int col) {
        if (col == 0) {
            cell.setCellStyle(styles.get("wbs1"));
        } else if (col == 1) {
            cell.setCellStyle(styles.get("wbs1"));
        } else if (col == 2 || col ==3) {
            cell.setCellStyle(styles.get("wbs1_num"));
            if (cell.getStringCellValue() != "") {
                cell.setCellValue(Integer.parseInt(cell.getStringCellValue()));
            }
        } else if (col == 4) {
            cell.setCellStyle(styles.get("wbs1_left"));
        }
    }

}
